USE [NLKHCNData_Prod]
GO

/****** Object:  StoredProcedure [dbo].[SearchChuyenGiaSimpleCount]    Script Date: 12/30/2014 11:09:08 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- 2014-12-30: hien created
-- EXEC SearchChuyenGiaSimpleCount '', '', '', 0, 4
-- =============================================

CREATE PROCEDURE [dbo].[SearchChuyenGiaSimpleCount] 
	@hoTen nvarchar(255) = '',
	@tenDonVi nvarchar(255) = '',
	@diaChiDonVi nvarchar(255) = '',
	@linhVucId int = 0,
	@nhomLinhVucId int = 0
AS
BEGIN

select count(*)
from ChuyenGia CG
	left outer join LinhVuc LV on CG.LinhVucId = LV.Id
	left outer join NhomLinhVuc NLV on LV.NhomLinhVucId = NLV.Id
where 
	(
		(@linhVucId = 0 and @nhomLinhVucId = 0)
		or (@linhVucId > 0 and LinhVucId = @linhVucId) 
		or (@linhVucId = 0 and @nhomLinhVucId > 0 and NLV.Id=@nhomLinhVucId)
	)
	and
	((@hoTen = '') or (HoTen like N'%' + @hoTen + '%')) and
	((@tenDonVi = '') or (TenDonVi like N'%' + @tenDonVi + '%')) and
	((@diaChiDonVi = '') or (DiaChiDonVi like N'%' + @diaChiDonVi + '%'))
	
END


GO


